package com.gzx.bi.modular.agent.mapper;

import com.gzx.bi.modular.agent.bo.GzxAgentEffectBo;
import com.gzx.bi.modular.agent.entity.GzxAgentEffect;
import com.gzx.bi.modular.agent.vo.GzxAgentCallPercentageVo;
import com.gzx.bi.modular.agent.vo.GzxAgentEffectVo;
import com.gzx.bi.modular.agent.vo.GzxAgentHourlyWorkloadVo;
import com.gzx.bi.modular.team.bo.GzxTeamEffectBo;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.common.mapper.BaseMapperPlus;

import java.util.List;

/**
 * 坐席效率
 */
public interface GzxAgentEffectMapper extends BaseMapperPlus<GzxAgentEffect, GzxAgentEffectVo> {

    // 批量插入
    @Insert({
            "<script>",
            "insert into gzx_project_debt(project_code,project_name,case_load,debt_user) " +
                    "values " +
                    " <foreach collection='list' item='item' separator=','>" +
                    " (#{item.projectCode},#{item.projectName},#{item.caseLoad},#{item.debtUser}) " +
                    " </foreach> " +
                    " on duplicate key update case_load = values(case_load),debt_user = values(debt_user) " +
                    "</script>"
    })
    void insertUpdateBatch(List<GzxAgentEffect> agentEffectList);

    //插入存在则更新
    @Insert({
            "<script>",
            "insert into gzx_agent_effect(agent_num,belong_team,belong_project,connect_num,callout_num,call_duration,effect_duration,effect_ten,effect_thirty,effect_sixty,effect_more,`current_time`) " +
                    " values(#{agentNum},#{belongTeam},#{belongProject},#{connectNum},#{calloutNum},#{connectDuration},#{effectDuration},#{effectTen},#{effectThirty},#{effectSixty} ,#{effectMore}, DATE_FORMAT(NOW(), '%Y-%m-%d %H')) " +
                    " ON DUPLICATE KEY UPDATE " +
                    " connect_num = values(connect_num) + #{connectNum} ,callout_num = values(callout_num) + #{calloutNum} ,call_duration = values(call_duration) + #{connectDuration} ,effect_duration = values(effect_duration) + #{effectDuration} , effect_ten = values(effect_ten) + #{effectTen} , effect_thirty = values(effect_thirty) + #{effectThirty} ,effect_sixty = values(effect_sixty) + #{effectSixty} ,effect_more = values(effect_more) + #{effectMore}  " +
                    "</script>"
    })
    void insertUpdate(GzxAgentEffect agent);

    //坐席效率
    @Select({
            "<script>",
            "select " +
                    " ud.name agentName," +
                    " ae.agent_num agentNum," +
                    " ae.belong_team belongTeam," +
                    " concat(sum(ae.connect_num),'/',sum(ae.callout_num)) calloutSit," +
                    " (sum(ae.connect_num)/sum(ae.callout_num)) rateConnect," +
                    " (sum(ae.call_duration)) callDuration," +
                    " (sum(ae.effect_duration)) effectDuration," +
                    " (sum(ae.effect_ten)/sum(ae.call_duration)) effectTen," +
                    " (sum(ae.effect_thirty)/sum(ae.call_duration)) effectThirty," +
                    " (sum(ae.effect_sixty)/sum(ae.call_duration)) effectSixty," +
                    " (sum(ae.effect_more)/sum(ae.call_duration)) effectMore" +
                    " from gzx_agent_effect ae  left join gzx_user_debt ud on ae.agent_num = ud.account " +
                    "<if test='currentTime != null and currentTime != \"\"'> where ae.`current_time` like concat(DATE_FORMAT(#{currentTime}, '%Y-%m-%d') ,'%') </if>" +
                    "<if test='belongProject != null and belongProject != \"\"'> AND ae.belong_project in ${belongProject} </if>" +
                    "<if test='agentNum != null and agentNum != \"\"'> AND ae.agent_num = #{agentNum} </if>" +
                    " GROUP BY ae.belong_team,ae.agent_num,ud.name" +
                    "</script>"
    })
    List<GzxAgentEffectVo> getAgentEffect(GzxAgentEffectBo bo);

    //各时点外呼工作量及接通率
    @Select({
            "<script>",
            "select " +
                    " `current_time` currentTime," +
                    " sum(callout_num) calloutNum," +
                    " sum(connect_num) connectNum," +
                    " sum(effect_duration) effectDuration," +
                    " (sum(callout_num)/sum(connect_num)) rateConnect," +
                    " (sum(effect_duration)/sum(connect_num)) effectConnect" +
                    " from gzx_agent_effect " +
                    "<if test='currentTime != null and currentTime != \"\"'>where `current_time` like concat(DATE_FORMAT(#{currentTime}, '%Y-%m-%d') ,'%') </if>" +
                    "<if test='belongProject != null and belongProject != \"\"'> AND belong_project in ${belongProject} </if>" +
                    "<if test='agentNum != null and agentNum != \"\"'> AND agent_num = #{agentNum} </if>" +
                    " GROUP BY `current_time`" +
                    "</script>"
    })
    List<GzxAgentHourlyWorkloadVo> getHourlyWorkload(GzxTeamEffectBo bo);

    //通话时长占比
    @Select({
            "<script>",
            "select " +
                    " (sum(effect_ten)/sum(effect_duration)) effectTen," +
                    " (sum(effect_thirty)/sum(effect_duration)) effectThirty," +
                    " (sum(effect_sixty)/sum(effect_duration)) effectSixty," +
                    " (sum(effect_more)/sum(effect_duration)) effectMore" +
                    " from gzx_agent_effect " +
                    "<if test='currentTime != null and currentTime != \"\"'>where `current_time` like concat(DATE_FORMAT(#{currentTime}, '%Y-%m-%d') ,'%') </if>" +
                    "<if test='belongProject != null and belongProject != \"\"'> AND belong_project in ${belongProject} </if>" +
                    "<if test='agentNum != null and agentNum != \"\"'> AND agent_num = #{agentNum} </if>" +
                    "</script>"
    })
    GzxAgentCallPercentageVo getCallPercentage(GzxTeamEffectBo bo);
}
